#!/bin/bash
#  执行这个脚本前  设备账号权重表 入库脚本执行存储
#                空设备和临时guid表  入库成功


# T日  要计算的数据日期
dt1=$(date -d"-5 days" +%Y-%m-%d)


export  JAVA_HOME=/opt/apps/jdk1.8.0_191
export  HADOOP_HOME=/opt/apps/hadoop-3.1.1
export  HIVE_HOME=/opt/apps/hive-3.1.2


sql="
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrick;
insert into  table   dwd.cool_app_log_dtl  partition (dt)
select
       t2.acc_id  as  guid  ,
       t1.account as  account ,
       t1.deviceid as  device_id ,
       t1.devicetype as device_type ,
       t1.sessionid as session_id ,
       t1.new_session as new_session ,
       t1.appid as  app_id ,
       t1.appversion as app_version ,
       t1.osname as  os_name ,
       t1.osversion as os_version,
       t1.carrier as  carrier ,
       t1.nettype as net_type ,
       t1.eventid as  event_id ,
       t1.properties as properties ,
       t1.ip as  ip  ,
       t1.latitude as  lat ,
       t1.longitude as lng ,
       t1.releasechannel as release_channel ,
       t1.resolution as resolution ,
       t1.timestamp as  ts  ,
       t1.province as province ,
       t1.city as  city  ,
       t1.region as region ,
       '${dt1}' as dt
from
(select
*
from tmp.cool_app_with_region
where dt  = '${dt1}'
and account is not null  and  trim(account) !=''
) t1
join
 dim.cool_user_info  t2
on t1.account = t2.account
union all
--  第二部分  没有账号的数据
select
       nvl(t2.guid , t3.guid)  as guid  ,
       t1.account as  account ,
       t1.deviceid as  device_id ,
       t1.devicetype as device_type ,
       t1.sessionid as session_id ,
       t1.new_session as new_session ,
       t1.appid as  app_id ,
       t1.appversion as app_version ,
       t1.osname as  os_name ,
       t1.osversion as os_version,
       t1.carrier as  carrier ,
       t1.nettype as net_type ,
       t1.eventid as  event_id ,
       t1.properties as properties ,
       t1.ip as  ip  ,
       t1.latitude as  lat ,
       t1.longitude as lng ,
       t1.releasechannel as release_channel ,
       t1.resolution as resolution ,
       t1.timestamp as  ts  ,
       t1.province as province ,
       t1.city as  city  ,
       t1.region as region
from
     (
         select *
        from tmp.cool_app_with_region
        where dt = '${dt1}'
        and  (account is  null or  trim(account) = '')
         )t1
left join
        (select
                o2.device_id  as  device_id ,
                o2.account  as account,
                o3.acc_id as  guid
                       from
                            (select
                                   device_id ,
                                   account
                            from
                            (select
                                    device_id ,
                                     account  ,
                                    row_number() over (partition by device_id order by weight desc)  rn
                                    from dim.cool_device_account_bind
                                    where dt = '${dt1}') o1
                            where rn = 1) o2
                join  dim.cool_user_info o3
         on o2.account = o3.account
       ) t2
on t1.deviceid = t2.device_id
left join
    dim.cool_empty_device_tmp_guid t3
on t1.deviceid = t3.device_id "


${HIVE_HOME}/bin/hive  -e "${sql}"

if [ $? -eq  0 ]; then
  echo  "入库成功"
  # 发送邮件  mail  -s    邮箱地理
  else  "入库失败"
fi



